#!/usr/bin/env node

const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('🔍 分析devices表中的对象类分布和专业分类一致性...\n');

try {
  console.log('1. 统计devices表中的总体情况...');
  
  // 获取devices表的总体统计
  const totalDevices = db.prepare('SELECT COUNT(*) as count FROM devices').get();
  console.log(`设备总数: ${totalDevices.count}`);

  console.log('\n2. 分析devices表中的对象类分布...');
  
  // 获取devices表中所有对象类的分布
  const deviceClasses = db.prepare(`
    SELECT 
      classification_name,
      specialty,
      COUNT(*) as device_count,
      COUNT(DISTINCT classification_code) as classification_code_count,
      GROUP_CONCAT(DISTINCT device_id ORDER BY device_id) as device_ids
    FROM devices 
    WHERE classification_name IS NOT NULL AND classification_name != ''
    GROUP BY classification_name, specialty
    ORDER BY specialty, classification_name
  `).all();

  console.log(`对象类总数: ${deviceClasses.length}`);

  let totalDevicesInClasses = 0;
  const specialtyStats = {};

  console.log('\n📋 对象类详细分布:');
  deviceClasses.forEach((cls, index) => {
    totalDevicesInClasses += cls.device_count;
    
    if (!specialtyStats[cls.specialty]) {
      specialtyStats[cls.specialty] = {
        classCount: 0,
        deviceCount: 0
      };
    }
    specialtyStats[cls.specialty].classCount++;
    specialtyStats[cls.specialty].deviceCount += cls.device_count;
    
    console.log(`\n${index + 1}. ${cls.classification_name} (${cls.specialty})`);
    console.log(`   设备数量: ${cls.device_count}`);
    console.log(`   分类代码数: ${cls.classification_code_count}`);
    if (cls.device_count <= 5) {
      console.log(`   设备ID: ${cls.device_ids}`);
    } else {
      const deviceIdList = cls.device_ids.split(',');
      console.log(`   设备ID: ${deviceIdList.slice(0, 3).join(', ')} ... (共${cls.device_count}个)`);
    }
  });

  console.log(`\n📊 对象类统计汇总:`);
  console.log(`- 对象类总数: ${deviceClasses.length}`);
  console.log(`- 包含在对象类中的设备总数: ${totalDevicesInClasses}`);
  console.log(`- 设备总数: ${totalDevices.count}`);
  
  if (totalDevicesInClasses !== totalDevices.count) {
    const missingDevices = totalDevices.count - totalDevicesInClasses;
    console.log(`⚠️  有 ${missingDevices} 个设备没有对象类信息`);
  }

  console.log(`\n🏢 按专业统计:`);
  Object.entries(specialtyStats).forEach(([specialty, stats]) => {
    console.log(`${specialty}: ${stats.classCount}个对象类, ${stats.deviceCount}个设备`);
  });

  console.log('\n3. 检查专业分类一致性...');
  
  // 检查devices表和device_type_mappings表之间的专业分类一致性
  console.log('\n正在检查两表之间的专业分类一致性...');
  
  const inconsistencies = [];
  let totalChecked = 0;
  let consistentCount = 0;

  // 获取所有设备的分类代码和专业信息
  const allDevices = db.prepare(`
    SELECT device_id, classification_name, specialty, classification_code
    FROM devices 
    WHERE classification_code IS NOT NULL AND classification_code != ''
    ORDER BY specialty, device_id
  `).all();

  console.log(`需要检查的设备数量: ${allDevices.length}`);

  allDevices.forEach(device => {
    totalChecked++;
    
    // 查找对应的映射记录
    const mappings = db.prepare(`
      SELECT classification_name, specialty, english_name
      FROM device_type_mappings 
      WHERE classification_code = ?
    `).all(device.classification_code);

    if (mappings.length === 0) {
      inconsistencies.push({
        device_id: device.device_id,
        issue: '缺失映射',
        device_class: device.classification_name,
        device_specialty: device.specialty,
        mapping_class: null,
        mapping_specialty: null,
        classification_code: device.classification_code
      });
    } else {
      // 检查每个映射记录
      mappings.forEach(mapping => {
        const classMatch = mapping.classification_name === device.classification_name;
        const specialtyMatch = mapping.specialty === device.specialty;
        
        if (classMatch && specialtyMatch) {
          consistentCount++;
        } else {
          inconsistencies.push({
            device_id: device.device_id,
            issue: classMatch ? '专业不一致' : (specialtyMatch ? '对象类不一致' : '完全不一致'),
            device_class: device.classification_name,
            device_specialty: device.specialty,
            mapping_class: mapping.classification_name,
            mapping_specialty: mapping.specialty,
            classification_code: device.classification_code
          });
        }
      });
    }
  });

  console.log(`\n📊 一致性检查结果:`);
  console.log(`- 检查的设备总数: ${totalChecked}`);
  console.log(`- 一致的设备数: ${consistentCount}`);
  console.log(`- 不一致的情况数: ${inconsistencies.length}`);
  console.log(`- 一致性比例: ${((consistentCount / totalChecked) * 100).toFixed(1)}%`);

  if (inconsistencies.length > 0) {
    console.log(`\n❌ 发现的不一致情况:`);
    
    // 按问题类型分组
    const issueGroups = {};
    inconsistencies.forEach(item => {
      if (!issueGroups[item.issue]) {
        issueGroups[item.issue] = [];
      }
      issueGroups[item.issue].push(item);
    });

    Object.entries(issueGroups).forEach(([issueType, items]) => {
      console.log(`\n  ${issueType} (${items.length}个):`);
      items.forEach(item => {
        console.log(`    ${item.device_id}:`);
        console.log(`      devices表: ${item.device_class} (${item.device_specialty})`);
        if (item.mapping_class) {
          console.log(`      mappings表: ${item.mapping_class} (${item.mapping_specialty})`);
        } else {
          console.log(`      mappings表: 无映射记录`);
        }
        console.log(`      分类代码: ${item.classification_code}`);
      });
    });
  } else {
    console.log(`\n✅ 所有设备的专业分类都是一致的!`);
  }

  console.log('\n4. 验证对象类和实例表的对应关系...');
  
  // 检查当前的实例表数量
  const instanceTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();

  console.log(`当前实例表数量: ${instanceTables.length}`);
  console.log(`对象类数量: ${deviceClasses.length}`);
  
  if (instanceTables.length === deviceClasses.length) {
    console.log(`✅ 实例表数量与对象类数量完全匹配!`);
  } else {
    console.log(`⚠️  实例表数量与对象类数量不匹配，差异: ${Math.abs(instanceTables.length - deviceClasses.length)}`);
  }

} catch (error) {
  console.error('❌ 分析失败:', error.message);
  console.error(error.stack);
} finally {
  db.close();
}